package project.web.generic.csv;

import java.util.Map;
import java.util.Map.Entry;
import java.util.Objects;

import online.model.Band;
import online.model.ModelUtil;
import online.model.check.InputCheck;
import online.struts.action.BaseAction;
import online.struts.action.UniForm;
import project.check.TopMessage;
import project.check.attribute.LongNumeralCheck;
import project.check.attribute.NumberCheck;
import project.check.attribute.NumeralCheck;
import project.check.existence.MustCheck;
import project.check.master.MasterCheck;
import project.check.master.NoMasterCheck;
import project.check.range.LengthRangeCheck;
import project.check.range.NumeralRangeCheck;
import project.common.StringUtil;
import project.common.db.DBColumnInfo;
import project.common.db.DBMetaData;
import project.master.MsgUtil;
import project.svc.generic.csv.define.DefineTestQuery;
import project.web.InstanceFactory;

import common.db.JdbcSource;

import core.config.Factory;

/**
 * 汎用CSV登録アクション
 * @author Tadashi Nakayama
 * @version 1.0.0
 */
public final class DefineAction extends BaseAction {

	/** キー項目 */
	private static final String ID = "Id";
	/** バージョン項目 */
	private static final String VERSION = "Version";
	/** 定義名 */
	private static final String DEFINE_NAME = "DefineName";
	/** スキーマ名 */
	private static final String SCHEMA_NAME = "SchemaName";
	/** 有効無効区分 */
	private static final String YKKB = "Ykkb";
	/** テーブル名 */
	private static final String TABLE_NAME = "TableName";
	/** タイトル名 */
	private static final String TITLE_NAME = "TitleName";
	/** アイテム名 */
	private static final String ITEM_NAME = "ItemName";
	/** クエリ */
	private static final String QUERY_FROM = "QueryFrom";
	/** クエリ */
	private static final String QUERY_WHERE = "QueryWhere";

	/**
	 * 初期表示
	 * @param model 汎用モデル
	 * @return 処理結果
	 */
	public String get(final UniForm model) {

		if (!model.hasValue(ID)) {
			InputCheck ic = InstanceFactory.create(InputCheck.class, model);
			ic.add(ID, new LongNumeralCheck());
			ic.populate();
		}

		if (CsvActionUtil.select("project.svc.generic.csv.define.SelectHeader", model)) {
			CsvActionUtil.select("project.svc.generic.csv.define.SelectDetail", model);
		} else {
			if (model.hasQueryString()) {
				return ID_NG_REDIRECT;
			}
			// メッセージ
			MsgUtil.putTopMessage(model, "ZZ000000014");
		}

		return ID_VIEW;
	}

	/**
	 * 行追加
	 * @param model 汎用モデル
	 * @return 処理結果
	 */
	public String add(final UniForm model) {

		InputCheck ic = InstanceFactory.create(InputCheck.class, model);
		ic.add(TABLE_NAME, new LengthRangeCheck(0, 64));
		ic.add(TITLE_NAME, new LengthRangeCheck(0, 100));
		ic.add(ITEM_NAME, new LengthRangeCheck(0, 100));
		ic.add(SCHEMA_NAME, new LengthRangeCheck(0, 32));
		ic.populate();

		if (model.getString(TABLE_NAME).isEmpty()) {
			model.addValue(TITLE_NAME, "");
			model.addValue(ITEM_NAME, "");
		} else {
			DBMetaData dmd = Factory.create(DBMetaData.class);
			Map<String, DBColumnInfo> info = dmd.getColumnInfo(model.getString(TABLE_NAME),
							toCorrectSchema(model.getString(SCHEMA_NAME)));
			if (info == null) {
				MsgUtil.putTopMessage(model, "ZZ000000005");
				return ID_NG;
			}

			for (final Entry<String, DBColumnInfo> meta : info.entrySet()) {
				String comm = meta.getValue().getComment();
				if (Objects.toString(comm, "").isEmpty()) {
					comm = meta.getKey();
				}
				model.addValue(TITLE_NAME, comm);
				model.addValue(ITEM_NAME, meta.getKey());
			}
		}

		return ID_VIEW;
	}

	/**
	 * 登録
	 * @param model 汎用モデル
	 * @return 処理結果
	 */
	public String register(final UniForm model) {

		checkInput(model);

		String schema = toCorrectSchema(model.getString(SCHEMA_NAME));
		if (!model.getString(SCHEMA_NAME).isEmpty() && schema == null) {
			MsgUtil.putTopMessage(model, "ZZ000000005");
			return ID_NG;
		}

		if (model.getString(ID).isEmpty()) {
			// 作成
			CsvActionUtil.select("project.svc.generic.csv.define.SelectId", model);
			model.aliasKey(ID, "DefineNo");
			CsvActionUtil.update("project.svc.generic.csv.define.InsertHeader", model);
			CsvActionUtil.updateArray(
							"project.svc.generic.csv.define.InsertDetail", model, TITLE_NAME);
			MsgUtil.putTopMessage(model, "ZZ000000020");
			return ID_OK;
		}

		// 更新
		if (1 != CsvActionUtil.update("project.svc.generic.csv.define.UpdateHeader", model)) {
			MsgUtil.putTopMessage(model, "ZZ000000019");
		} else {
			CsvActionUtil.select("project.svc.generic.csv.define.SelectHeader", model);
			CsvActionUtil.update("project.svc.generic.csv.define.DeleteDetail", model);
			CsvActionUtil.updateArray(
							"project.svc.generic.csv.define.InsertDetail", model, TITLE_NAME);
			MsgUtil.putTopMessage(model, "ZZ000000020");
			return ID_OK;
		}

		return ID_VIEW;
	}

	/**
	 * クエリダウンロード
	 * @param model 汎用モデル
	 * @return 処理結果
	 */
	public String download(final UniForm model) {
		checkInput(model);

		model.setValue("DefineQuery", getQuery(model));

		return "DOWNLOAD";
	}

	/**
	 * 抽出項目設定
	 * @param model 汎用モデル
	 * @return 処理結果
	 */
	public String append(final UniForm model) {

		checkInputAppend(model);

		String schema = toCorrectSchema(model.getString(SCHEMA_NAME));
		if (!model.getString(SCHEMA_NAME).isEmpty() && schema == null) {
			MsgUtil.putTopMessage(model, "ZZ000000005");
			return ID_NG;
		}

		DefineTestQuery tq = InstanceFactory.create(DefineTestQuery.class, model);
		if (!tq.tryQuery(schema,
						model.getString(QUERY_FROM) + "\n" + model.getString(QUERY_WHERE))) {
			model.setValue(ModelUtil.TAG_MESSAGE, tq.getMessage());
			return ID_NG;
		}

		model.addValue(ITEM_NAME, tq.getItem().toArray(new String[tq.getItem().size()]));
		model.addValue(TITLE_NAME, new String[tq.getItem().size()]);

		return ID_VIEW;
	}

	/**
	 * クエリテスト
	 * @param model 汎用モデル
	 * @return 処理結果
	 */
	public String test(final UniForm model) {

		checkInputAppend(model);

		String schema = toCorrectSchema(model.getString(SCHEMA_NAME));
		if (!model.getString(SCHEMA_NAME).isEmpty() && schema == null) {
			MsgUtil.putTopMessage(model, "ZZ000000005");
			return ID_NG;
		}

		DefineTestQuery tq = InstanceFactory.create(DefineTestQuery.class, model);
		if (!tq.tryQuery(schema, getQuery(model))) {
			model.setValue(ModelUtil.TAG_MESSAGE, tq.getMessage());
			return ID_NG;
		}

		MsgUtil.putTopMessage(model, "ZZ000000020");
		return ID_VIEW;
	}

	/**
	 * クエリ取得
	 * @param model 汎用モデル
	 * @return クエリ
	 */
	private String getQuery(final UniForm model) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT\n");
		for (final Band band : Band.iterable(model, ITEM_NAME)) {
			sb.append("  F.").append(band.string(ITEM_NAME));
			if (!band.string(TITLE_NAME).isEmpty()) {
				sb.append(" AS ").append(band.string(TITLE_NAME));
			}
			if (band.hasNext()) {
				sb.append(",");
			}
			sb.append("\n");
		}
		sb.append("FROM (\n");
		sb.append(model.getString(QUERY_FROM)).append("\n");
		sb.append(model.getString(QUERY_WHERE));
		sb.append(") F \n");
		return sb.toString();
	}

	/**
	 * 入力チェック
	 * @param model 汎用モデル
	 */
	private void checkInput(final UniForm model) {
		InputCheck ic = InstanceFactory.create(InputCheck.class, model);
		ic.onError(new TopMessage("ZZ000000005"));
		ic.add(ID, new LongNumeralCheck());
		ic.add(VERSION, new MustCheck(), new NumeralCheck());
		ic.add(DEFINE_NAME, new MustCheck(), new LengthRangeCheck(0, 100));
		ic.add(SCHEMA_NAME, new LengthRangeCheck(0, 32));
		ic.add(YKKB, new MustCheck(), new NumberCheck(), new NumeralRangeCheck(0, 1));
		ic.add(TITLE_NAME, new MustCheck());
		ic.add(ITEM_NAME, new MustCheck());
		ic.add(QUERY_FROM, new MustCheck());
		ic.add(QUERY_WHERE, new LengthRangeCheck(0, 4096));
		ic.populate();

		if (model.getNumber(ID) == null) {
			ic.onError(new TopMessage("ZZ000000021"));
			ic.add(DEFINE_NAME, new NoMasterCheck(
							null, "project.svc.generic.csv.define.SelectPattern"));
		} else {
			ic.onError(new TopMessage("ZZ000000018"));
			ic.add(ID, new MasterCheck(null, "project.svc.generic.csv.define.SelectHeader"));
		}
		ic.check();

		model.setValue(TITLE_NAME, toTrimmedFullWidth(model.getStringArray(TITLE_NAME)));
		model.setValue(ITEM_NAME, StringUtil.trim(model.getStringArray(ITEM_NAME)));
		model.setValue(QUERY_FROM, sanitize(model.getString(QUERY_FROM)));
		model.setValue(QUERY_WHERE, sanitize(model.getString(QUERY_WHERE)));
	}

	/**
	 * 入力チェック
	 * @param model 汎用モデル
	 */
	private void checkInputAppend(final UniForm model) {
		InputCheck ic = InstanceFactory.create(InputCheck.class, model);
		ic.onError(new TopMessage("ZZ000000005"));
		ic.add(SCHEMA_NAME, new LengthRangeCheck(0, 32));
		ic.add(TITLE_NAME, new LengthRangeCheck(0, 100));
		ic.add(ITEM_NAME, new LengthRangeCheck(0, 100));
		ic.add(QUERY_FROM, new MustCheck());
		ic.add(QUERY_WHERE, new LengthRangeCheck(0, 4096));
		ic.populate();

		model.setValue(TITLE_NAME, toTrimmedFullWidth(model.getStringArray(TITLE_NAME)));
		model.setValue(ITEM_NAME, StringUtil.trim(model.getStringArray(ITEM_NAME)));
		model.setValue(QUERY_FROM, sanitize(model.getString(QUERY_FROM)));
		model.setValue(QUERY_WHERE, sanitize(model.getString(QUERY_WHERE)));
	}

	/**
	 * サニタイズ
	 * @param val 文字列
	 * @return サニタイズ文字列
	 */
	private String sanitize(final String val) {
		return val.replaceAll("/\\*[:\\$\\?@].+\\*/", "").trim();
	}

	/**
	 * .を全角変換とトリム
	 * @param val 配列
	 * @return 変換後配列
	 */
	private String[] toTrimmedFullWidth(final String[] val) {
		for (int i = 0; i < val.length; i++) {
			val[i] = val[i].trim().replace(".", "．");
		}
		return val;
	}

	/**
	 * スキーマ名取得
	 * @param schema スキーマ名
	 * @return スキーマ名
	 */
	private String toCorrectSchema(final String schema) {
		return JdbcSource.hasResource(schema) ? schema : null;
	}
}
